mysql 分组group by结合case when 二次分组用法

2024-07-07 03:29| 来源: 网络整理| 查看: 265

第一部分：分组之后每种状态，再分组归为四类，每类再分组

不建议用法是,，或者说错误用法是

语法通过但是得不到正确的结果

sum 。。。group by case when

比如如下写法：

select customer_id,product_key,product_name,product_version ,max(operation_time) as operation_time,action, case when action ='通知发送' or action = '通知重复发送' then '已发送' when action ='通知被同意' or action ='通知被拒绝' then '回复' when action ='推包' then '已推包' else action end action_type,case when action = '通知被拒绝' then 0 else 1 end status from bi_product_action_list group by customer_id,product_key,product_name,product_version,case when action ='通知发送' or action = '通知重复发送' then '已发送' when action ='通知被同意' or action ='通知被拒绝' then '回复' when action ='推包' then '已推包' else action end

正确写法

分组后再分组

select customer_id,product_key,product_name,product_version ,max(operation_time) as operation_time,action_type from ( select customer_id,product_key,product_name,product_version ,max(operation_time) as operation_time,action, case when action ='通知发送' or action = '通知重复发送' then '已发送' when action ='通知被同意' or action ='通知被拒绝' then '回复' when action ='推包' then '已推包' else action end action_type, case when action = '通知被拒绝' then 0 else 1 end status from bi_product_action_list group by customer_id,product_key,product_name,product_version,action ) m group by customer_id,product_key,product_name,product_version,action_type

第二部分：常规用法

group by case when

原表是个员工档案，共583人，但case when结合group by用时，写法不同，其出来的结果也不同

例1：

select distinct a.Branch, case when kultur = '硕士' then sum(num) else '0' end as 研究生学历, case when kultur = '本科' then sum(num) else '0' end as 大本学历, case when kultur = '大专' then sum(num) else '0' end as 大专学历, case when kultur = '中专' then sum(num) else '0' end as 中专学历, case when kultur = '高中' or kultur ='职高' or kultur ='中职' then sum(num) else '0' end as 高中学历, case when kultur = '初中' then sum(num) else '0' end as 初中学历, case when kultur = '小学' then sum(num) else '0' end as 小学学历, case when kultur = '' or kultur is null then sum(num) else '0' end as 未填写学历 from( select Branch,kultur,count(*) as num from tb_Stuffbusic group by branch,kultur) a Group by a.Branch,a.kultur

结果如下图：

例2：

select Branch as 部门名称,count(branch) as 总人数, sum(case when kultur = '硕士' then 1 else '0' end) as 研究生学历, sum(case when kultur = '本科' then 1 else '0' end) as 大本学历, sum(case when kultur = '大专' then 1 else '0' end) as 大专学历, sum(case when kultur = '中专' then 1 else '0' end) as 中专学历, sum(case when kultur = '高中' or kultur ='职高' or kultur ='中职' then 1 else '0' end) as 高中学历, sum(case when kultur = '初中' then 1 else '0' end) as 初中学历, sum(case when kultur = '小学' then 1 else '0' end) as 小学学历, sum(case when kultur = '' or kultur is null then 1 else '0' end) as 未填写学历 from tb_Stuffbusic group by branch

结果如下图：

例3：表中ID字段有时字段值长不够4位，但不能超过4位，故通过下面语句一次性加上。 UPDATE tb_Stuffbusic SET ID = CASE WHEN LEN(ID) = 1 THEN '000'+ID WHEN LEN(ID) = 2 THEN '00'+ID WHEN LEN(ID) = 3 THEN '0'+ID ELSE ID END

【本文地址】

公司简介

联系我们